import xlrd, xlwt
from xlutils.copy import copy

path = 'C:/Users/-/Desktop/test.xls'

data1 = xlrd.open_workbook(path)
# table = data.sheet_by_index(0)
table = data1.sheets()[0]
nrows = table.nrows
ncols = table.ncols

# 利用xlutils.copy函数，将xlrd.Book转为xlwt.Workbook，再用xlwt模块进行存储
data2 = copy(data1)
table2 = data2.get_sheet(0)
# table2.write(1, 3, '455656')

# data2.save(path)

with open('alias.txt', mode='r', encoding='utf-8') as f:
    origin_list = [(line.split()[0], line.split()[1].split(',')) for line in f.read().splitlines()]

# print(origin_list)

for name, alias in origin_list:
    for i in range(nrows):
        if table.row_values(i)[2].strip() == name.strip():
            print(name)
            if table.row_values(i)[3].strip():
                table2.write(i, 3, ','.join(list(set(table.row_values(i)[3].strip().split(',') + alias))))
            else:
                table2.write(i, 3, ','.join(alias))
data2.save(path)
